﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Fishing.Model;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace Fishing.Dal
{

    public class ShipDal
    {

        private static readonly Logger _logger = LogManager.GetCurrentClassLogger();


        /// <summary>
        /// ConStr
        /// </summary>
        private static readonly string ConStr = ConfigurationManager.ConnectionStrings["_fishingdb"].ConnectionString;

        public bool InsertDate(t_ship ship)
        {

            var ret = false;

            const string sql = "insert into t_ship values " +
                               "(@shipID,@contact,@phone,@shipName,@shipCaptain,@shipPhone,@shipWharf,@shipAmount,@fishingPrice,@fishingQty,@res,@status,now())";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@shipID", MySqlDbType.VarChar, 10).Value = ship.shipID;
            command.Parameters.Add("@contact", MySqlDbType.VarChar, 20).Value = ship.contact;
            command.Parameters.Add("@phone", MySqlDbType.VarChar, 20).Value = ship.phone;
            command.Parameters.Add("@shipName", MySqlDbType.VarChar, 20).Value = ship.shipName;
            command.Parameters.Add("@shipCaptain", MySqlDbType.VarChar, 20).Value = ship.shipCaptain;
            command.Parameters.Add("@shipPhone", MySqlDbType.VarChar, 20).Value = ship.shipPhone;
            command.Parameters.Add("@shipWharf", MySqlDbType.VarChar, 100).Value = ship.shipWharf;
            command.Parameters.Add("@shipAmount", MySqlDbType.Decimal, 9).Value = ship.shipAmount;
            command.Parameters.Add("@fishingPrice", MySqlDbType.Decimal, 9).Value = ship.fishingPrice;
            command.Parameters.Add("@fishingQty", MySqlDbType.Int32).Value = ship.fishingQty;
            command.Parameters.Add("@res", MySqlDbType.VarChar, 200).Value = ship.RES;
            command.Parameters.Add("@status", MySqlDbType.VarChar, 10).Value = ship.status;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("InsertDate ERROR !");
                _logger.Error("data : " + ship.ToString());
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public List<t_ship> GetShipDataBy(string statue, string starttime, string endtime, string like)
        {
            var retList = new List<t_ship>();
            var sql = "select * from t_ship  where 1=1 ";
            if (statue.Length > 0)
            {
                sql = sql + " and `status`='" + statue + "' ";
            }
            if (starttime.Length > 0)
            {
                sql = sql + " and createTime > =DATE('" + starttime + "') ";
            }
            if (endtime.Length > 0)
            {
                sql = sql + " and createTime <= DATE('" + endtime + "') ";
            }
            if (like.Length > 0)
            {
                sql = sql + " and (contact like '%" + like + "%' or shipName like '%" + like + "%' or shipCaptain like '%" + like + "%' or shipWharf like '%" + like + "%') ";
            }



            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            try
            {
                connection.Open();
                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            retList.Add(new t_ship()
                            {
                                shipID = reader["shipID"].ToString(),
                                contact = reader["contact"].ToString(),
                                phone = reader["phone"].ToString(),
                                shipName = reader["shipName"].ToString(),
                                shipCaptain = reader["shipCaptain"].ToString(),
                                shipPhone = reader["shipPhone"].ToString(),
                                shipWharf = reader["shipWharf"].ToString(),
                                shipAmount = reader["shipAmount"].ToString(),
                                fishingPrice = reader["fishingPrice"].ToString(),
                                fishingQty = reader["fishingQty"].ToString(),
                                RES = reader["RES"].ToString(),
                                status = reader["status"].ToString()
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("GetShipDataBy t_ship ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();
            }
            return retList;

        }


        public bool UpdateDate(string IDs, string status)
        {

            var ret = false;

             string sql = "update  t_ship  set `status`='"+ status + "' where  shipID in ("+ IDs + ")";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("UpdateDate t_ship  ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;

        }
    }
}